﻿
---BUOC 1: KEO BANG LIMIT_ORG VE MAY

SELECT BUSINESS_DATE, LIABILITY_NUMBER, ID,  APPROVAL_DATE, EXPIRY_DATE, ONLINE_LIMIT_DATE,
PRODUCT_ALLOWED, INTERNAL_AMOUNT, FIXED_VARIABLE, COLLATERAL_CODE, MAXIMUM_SECURED, 
MAXIMUM_UNSECURED, MAXIMUM_TOTAL, COLLAT_RIGHT, SECURED_AMT, ONLINE_LIMIT,TOTAL_OS, AVAIL_AMT,
CREDIT_LINE, LIMIT_PRODUCT, CO_CODE, DEPT_CODE, LIMIT_CURRENCY
INTO S74_LIMIT_DAILY
FROM SERVER74.STAGING.[DBO].[DLYTB_LIMIT_ORG] 
WHERE  (ID LIKE '%.005%' OR ID LIKE '%.006%')
AND LIABILITY_NUMBER IN (SELECT CIF FROM TBL_CUSTOMER)

--DROP TABLE LIMIT_ORG_QD

DECLARE @DATE NVARCHAR(8)= (SELECT CONVERT(NVARCHAR(8),MAX( BUSINESS_DATE), 112) FROM SERVER74.STAGING.[DBO].[DLYTB_LIMIT_ORG] )
SELECT A.*
,B.MID_RATE
,B.MID_RATE* 
CONVERT(FLOAT,(CASE WHEN INTERNAL_AMOUNT LIKE '%B%'THEN REPLACE(INTERNAL_AMOUNT,'B','000000000')
WHEN INTERNAL_AMOUNT LIKE'%M%'THEN REPLACE(INTERNAL_AMOUNT,'M','000000') 
ELSE INTERNAL_AMOUNT END)) AS INTENRNAL_AMOUNT_QD,
B.MID_RATE* 
CONVERT(FLOAT, (CASE WHEN ONLINE_LIMIT LIKE '%B%'THEN REPLACE(ONLINE_LIMIT,'B','000000000')
WHEN ONLINE_LIMIT LIKE'%M%'THEN REPLACE(ONLINE_LIMIT,'M','000000') 
ELSE ONLINE_LIMIT END)) AS ONLINE_LIMIT_QD,
B.MID_RATE* 
CONVERT(FLOAT, (CASE WHEN TOTAL_OS LIKE'%B%'THEN REPLACE(TOTAL_OS,'B','000000000')
WHEN TOTAL_OS LIKE'%M%'THEN REPLACE(TOTAL_OS,'M','000000') 
ELSE TOTAL_OS END)) AS TOTAL_OS_QD
,B.MID_RATE* 
CONVERT(FLOAT,(CASE WHEN AVAIL_AMT LIKE'%B%'THEN REPLACE(AVAIL_AMT,'B','000000000')
WHEN AVAIL_AMT LIKE'%M%'THEN REPLACE(AVAIL_AMT,'M','000000') 
ELSE AVAIL_AMT END)) AS AVAIL_AMT_QD
            ,B.MID_RATE* 
CONVERT(FLOAT,(CASE WHEN MAXIMUM_UNSECURED LIKE'%B%'THEN REPLACE(MAXIMUM_UNSECURED,'B','000000000')
WHEN MAXIMUM_UNSECURED LIKE'%M%'THEN REPLACE(MAXIMUM_UNSECURED,'M','000000') 
ELSE MAXIMUM_UNSECURED END) ) AS MAXIMUM_UNSECURED_QD
            INTO S74_LIMIT_DAILY_QD
FROM S74_LIMIT_DAILY A
LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
ON A.LIMIT_CURRENCY=B.CODE
WHERE SAVE_DATE = @DATE

drop table LIMIT_DAILY_BY_TYPE

select * from S74_LIMIT_DAILY_QD where LIABILITY_NUMBER = '1945531'

SELECT CASE WHEN ID LIKE '%.005%' THEN N'A. CẤP TÍN DỤNG THEO HẠN MỨC'
			WHEN ID LIKE '%.006%' THEN N'B. CẤP TÍN DỤNG THEO MÓN/ TỪNG LẦN' ELSE NULL END AS GROUP_TYPE_LIMIT,
		CASE WHEN ID LIKE '%.0050000%' THEN N'TỔNG CẤP TÍN DỤNG THEO HẠN MỨC'
			WHEN ID LIKE '%.0058100%' THEN N'CHO VAY THEO HẠN MỨC'
			WHEN ID LIKE '%.0058200%' THEN N'CHIẾT KHẤU/ LC/ BL'
			WHEN ID LIKE '%.0058300%' THEN N'CHIẾT KHẤU/ LC/ BL'
			WHEN ID LIKE '%.0058400%' THEN N'BẢO LÃNH'
			WHEN ID LIKE '%.0058500%' THEN N'CHIẾT KHẤU/ LC/ BL'
			WHEN ID LIKE '%.0058600%' THEN N'THẤU CHI'
			WHEN ID LIKE '%.0058700%' THEN N'THẺ'
			WHEN ID LIKE '%.0060000%' THEN N'TỔNG CẤP TÍN DỤNG THEO MÓN/TỪNG LẦN'
			WHEN ID LIKE '%.0066400%' THEN N'CHO VAY MÓN'
			WHEN ID LIKE '%.0066500%' THEN N'CHIẾT KHẤU/ LC/ BL'
			WHEN ID LIKE '%.0066600%' THEN N'CHIẾT KHẤU/ LC/ BL'
			WHEN ID LIKE '%.0066700%' THEN N'CHIẾT KHẤU/ LC/ BL' ELSE NULL END AS TYPE_LIMIT,* 
INTO LIMIT_DAILY_BY_TYPE
FROM S74_LIMIT_DAILY_QD

DROP TABLE TBL_REDUCE_LIMIT_INFO

SELECT 
--*
B.GROUP_TYPE_LIMIT, B.TYPE_LIMIT, B.TYPE_ORDER, LIABILITY_NUMBER AS CIF, SUM(ONLINE_LIMIT_QD) AS ONLINE_LIMIT_QD, SUM(TOTAL_OS_QD) AS TOTAL_OS_QD, SUM(AVAIL_AMT_QD) AS AVAIL_AMT_QD
INTO TBL_REDUCE_LIMIT_INFO
FROM LIMIT_DAILY_BY_TYPE A, TYPE_LIMIT_DIC B
WHERE 
A.GROUP_TYPE_LIMIT = B.GROUP_TYPE_LIMIT AND A.TYPE_LIMIT = B.TYPE_LIMIT AND 
A.TYPE_LIMIT IS NOT NULL AND ONLINE_LIMIT_QD IS NOT NULL
--AND LIABILITY_NUMBER = '1299007'
--ORDER BY TYPE_LIMIT, LIABILITY_NUMBER
GROUP BY B.GROUP_TYPE_LIMIT, B.TYPE_LIMIT, LIABILITY_NUMBER, B.TYPE_ORDER
--166656000.00

SELECT GROUP_TYPE_LIMIT, TYPE_LIMIT, 0 AS TYPE_ORDER 
--INTO TYPE_LIMIT_DIC 
FROM LIMIT_DAILY_BY_TYPE 
where TYPE_LIMIT is not null
GROUP BY GROUP_TYPE_LIMIT, TYPE_LIMIT
--UPDATE TBL_REDUCE_LIMIT_INFO 
--SET TOTAL_OS_QD = 0 WHERE TOTAL_OS_QD IS NULL


update TBL_REDUCE_LIMIT_INFO
set TOTAL_OS_QD = 0 
where TOTAL_OS_QD is null


select * from TBL_REDUCE_LIMIT_INFO where TOTAL_OS_QD is null or AVAIL_AMT_QD is null or ONLINE_LIMIT_QD is null

SELECT TYPE_LIMIT, LIABILITY_NUMBER AS CIF,A.ID, B.LIMIT_REFERENCE, B.* FROM LIMIT_DAILY_BY_TYPE A, SERVER74.BICDATA.DBO.LNTB_MASTER B
WHERE A.ID = B.LIMIT_REFERENCE

SELECT * FROM TBL_REDUCE_LIMIT_INFO where cif = '1945531'

SELECT CIF, SUM(BAL_QD) FROM #TAM GROUP BY CIF

SELECT * FROM LIMIT_DAILY_BY_TYPE WHERE LIABILITY_NUMBER = '1299007'

DELETE FROM #TAM
SELECT * FROM #TAM WHERE CIF = '1299007'
INSERT #TAM 
SELECT * FROM SERVER74.BICDATA.DBO.BALANCE_LIVE WHERE BUSINESS_DATE = '2017-02-25' AND CIF = '1299007' AND APP = 'LOAN' 


SELECT LIMIT_REFERENCE, * FROM SERVER74.BICDATA.DBO.LNTB_MASTER WHERE ACCTNO = 'LD1415000486'

SELECT * FROM LIMIT_DAILY_BY_TYPE WHERE LIABILITY_NUMBER = '1221199'
--CHECK TAI SAN DAM BAO CUA CIF
SELECT TOP 10 * FROM HUONGTT19.CARD.[DBO].[T24_COLLATERAL_20170201_QD] WHERE CUSTOMER_ID= '1221199'